﻿CREATE PROCEDURE [dbo].[books_select_all]
AS
SELECT     [dbo].[Books].[BookId],
    [dbo].[Books].[DateOfPublish],
    [dbo].[Books].[ShortContent],
    [dbo].[Books].[Title], 
    [dbo].[Books].[Avatar],
    [dbo].[Books].[FileName],
    [dbo].[Books].[UserId],
   (SELECT DISTINCT CAST(BooksByAuthor.AuthorId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByAuthor
                     WHERE  BooksByAuthor.BookId = Books.BookId FOR xml path('')) AS authors,
   (SELECT DISTINCT CAST(BooksByCategory.CategoryId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByCategory
                     WHERE  BooksByCategory.BookId = Books.BookId FOR xml path('')) AS categories
FROM Books 

RETURN 0
